﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

using VK.Comm;
using VK.Comm.TransactSql;

namespace VK.Casemeet.Groups
{
    public class CategoryDAL
    {
        public  static int InsertCategory(CategoryEntity category)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string InsertCategory = @"
            DECLARE @maxSort AS INT;
            SELECT @maxSort=MAX([gc_sort]) FROM [GroupCategory];
            INSERT INTO [GroupCategory]
            ([gc_name],[gc_sort],[gc_isenable])
             VALUES
            (@gc_name,ISNULL(@maxSort,0)+1,@gc_isenable)";

            DbCommand InsertCategoryCommand = db.GetSqlStringCommand(InsertCategory);
            db.AddInParameter(InsertCategoryCommand, "@gc_name", DbType.String, category.gc_name);
            db.AddInParameter(InsertCategoryCommand, "@gc_isenable", DbType.Boolean, category.gc_isenable);
            result = db.ExecuteNonQuery(InsertCategoryCommand);

            return result;
        }

        public static List<CategoryEntity> GetCategories(int gc_id,string condition="")
        {
            string selCategory = "select [gc_id],[gc_name],[gc_sort],[gc_isenable] from GroupCategory where 1=1";
            if (gc_id != 0)
                selCategory += " and gc_id=@gc_id";
            if (condition != "")
                selCategory += " and gc_name like '%"+condition+"%'";
            selCategory += " order by gc_sort desc";
            Database db = DatabaseFactory.CreateDatabase();
            List<CategoryEntity> categorylst = new List<CategoryEntity>();
            DbCommand SelectCategoryCommand = db.GetSqlStringCommand(selCategory);
           
            if (gc_id != 0)
                db.AddInParameter(SelectCategoryCommand, "@gc_id", DbType.Int32, gc_id);

            using (IDataReader dr = db.ExecuteReader(SelectCategoryCommand))
            {
                while (dr.Read())
                {
                    CategoryEntity category = GetEntity(dr);
                    categorylst.Add(category);
                }
            }

            return categorylst;
        }

        public static int DelCategoryEntity(int gc_id)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string DeleteCategory = @"DELETE FROM [GroupCategory] WHERE gc_id=@gc_id";
            DbCommand delgroupCommand = db.GetSqlStringCommand(DeleteCategory);
            db.AddInParameter(delgroupCommand, "@gc_id", DbType.Int32, gc_id);
            return db.ExecuteNonQuery(delgroupCommand);
        }

        public static int UpdateCategory(CategoryEntity categoryEntity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string upCategory = @"update [GroupCategory]
                                set gc_name=@gc_name WHERE gc_id=@gc_id";
            DbCommand upgroupCommand = db.GetSqlStringCommand(upCategory);
            db.AddInParameter(upgroupCommand, "@gc_id", DbType.Int32, categoryEntity.gc_id);
            db.AddInParameter(upgroupCommand, "@gc_name", DbType.String, categoryEntity.gc_name);
            return db.ExecuteNonQuery(upgroupCommand);
        }

        public static int SetCategoryMoveUp(int categoryID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetCategoryMoveUp = @"
/*   1：成功  2：已经在第一位无需移动   */
	DECLARE @sort AS INT, @preSort AS INT, @preCategoryID AS INT, @minSort AS INT
	DECLARE @RESULT AS INT; SET @RESULT=0;
	
	BEGIN TRANSACTION T_GROUP_Category_MoveUp
	
	SELECT @sort=gc_sort FROM GroupCategory WHERE gc_id=@CategoryID
	SELECT @minSort=MIN(gc_sort) FROM GroupCategory
	IF @sort<>@minSort
	BEGIN
		SELECT TOP 1 @preCategoryID=gc_id, @preSort=gc_sort FROM GroupCategory WHERE gc_sort<@sort ORDER BY gc_sort DESC
		UPDATE GroupCategory SET gc_sort=@preSort WHERE gc_id=@CategoryID
		UPDATE GroupCategory SET gc_sort=@sort WHERE gc_id=@preCategoryID
		SET @RESULT=1 
	END
	ELSE
	BEGIN
		SET @RESULT=2
	END
	
	IF @@ERROR=0 
		BEGIN
		COMMIT TRANSACTION T_GROUP_Category_MoveUp
		END
	ELSE
		BEGIN
		ROLLBACK TRANSACTION T_GROUP_Category_MoveUp
		END
		
	SELECT @RESULT";

            DbCommand InsertProductCommand = db.GetSqlStringCommand(SetCategoryMoveUp);
            db.AddInParameter(InsertProductCommand, "@CategoryID", DbType.Int32, categoryID);
            result = db.ExecuteNonQuery(InsertProductCommand);

            return result;
        
        }


        /// <summary>
        /// 向下移一位
        /// </summary>
        /// <param name="categoryID"></param>
        /// <returns>1：成功  2：已经在最后无需移动</returns>
        internal static int SetCategoryMoveDown(int categoryID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();
            // [gc_id],[gc_name],[gc_sort],[gc_isenable] from GroupCategory
            string SetCategoryMoveDown = @"
    /*   1：成功  2：已经在最后无需移动   */
	DECLARE @sort AS INT, @nextSort AS INT, @nextCategoryID AS INT, @maxSort AS INT
	DECLARE @RESULT AS INT; SET @RESULT=0;
	
	BEGIN TRANSACTION T_GROUP_Category_MoveDown
	
	SELECT @sort=gc_sort FROM GroupCategory WHERE gc_id=@CategoryID
	SELECT @maxSort=MAX(gc_sort) FROM GroupCategory 
	IF @sort<>@maxSort
	BEGIN
		SELECT TOP 1 @nextCategoryID=gc_id, @nextSort=gc_sort FROM GroupCategory WHERE gc_sort>@sort ORDER BY gc_sort
		UPDATE GroupCategory SET gc_sort=@nextSort WHERE gc_id=@CategoryID
		UPDATE GroupCategory SET gc_sort=@sort WHERE gc_id=@nextCategoryID
		SET @RESULT=1 
	END
	ELSE
	BEGIN
		SET @RESULT=2
	END
	
	IF @@ERROR=0 
		BEGIN
		COMMIT TRANSACTION T_GROUP_Category_MoveDown
		END
	ELSE
		BEGIN
		ROLLBACK TRANSACTION T_GROUP_Category_MoveDown
		END
		
	SELECT @RESULT";

            DbCommand InsertProductCommand = db.GetSqlStringCommand(SetCategoryMoveDown);
            db.AddInParameter(InsertProductCommand, "@CategoryID", DbType.Int32, categoryID);
            result = db.ExecuteNonQuery(InsertProductCommand);

            return result;
        }



        private static CategoryEntity GetEntity(IDataReader dr)
        {
            CategoryEntity categoryentity = new CategoryEntity();
            categoryentity.gc_id = dr.GetInt32(0);
            categoryentity.gc_name = dr.GetString(1);
            categoryentity.gc_sort = dr.GetInt32(2);
            categoryentity.gc_isenable = dr.GetBoolean(3);

            return categoryentity;
        }
    }
}
